package dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Subject;
import model.User;

public class SubjectMod {
	Connection connection;
	PreparedStatement sql = null;
	private static SubjectMod isntanceSJ;
	public static SubjectMod getIsntance() {
		if (isntanceSJ == null) {
			isntanceSJ = new SubjectMod();
		}
		return isntanceSJ;
	}
	public boolean isSjID(int sjid) throws IOException{ /*kiem tra tinh hop le cua nid va co nam trong DB hay khong*/
		connection =  DB_Connection.getIsntance().getConnection();
		boolean result = false;
		try {
			sql = connection.prepareStatement("SELECT sjid FROM subject where tid = ? ");
			sql.setInt(1, sjid);
			ResultSet rs = sql.executeQuery();
			while (rs.next()) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		return result;
	}
	public boolean addSubject(Subject subject) {
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sqli = "INSERT INTO subject ( mmh , name )  VALUES( ? , ? ) ";
			sql = connection.prepareStatement(sqli);
			sql.setInt(3, subject.getMmh());
			sql.setString(6, subject.getName());
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpdateSubjectBySid(Subject subject, int sid) {
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sqlu = "UPDATE teachers SET  mmh = ?, name = ? WHERE sjid = ? LIMIT = 1 ";
			sql = connection.prepareStatement(sqlu);
			sql.setInt(1, subject.getMmh());
			sql.setString(2, subject.getName());
			sql.setInt(3, sid);
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpdateSubjectByMmh(Subject subject, int mmh) {
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sqlu = "UPDATE teachers SET  mmh = ?, name = ? WHERE mmh = ? LIMIT = 1 ";
			sql = connection.prepareStatement(sqlu);
			sql.setInt(1, subject.getMmh());
			sql.setString(2, subject.getName());
			sql.setInt(3, mmh);
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean delBySjid(int sid){
		if(AssignedSubjectsMod.getIsntance().isExitSjidInTable(sid)){
			if(!AssignedSubjectsMod.getIsntance().delBySjid(sid))return false;
		}
		PreparedStatement delpd = null;
		connection = DB_Connection.getIsntance().getConnection();
		boolean result = false;
		try {
			
			delpd = connection.prepareStatement("DELETE FROM subject WHERE sjid = ? LIMIT 1");
			delpd.setInt(1, sid);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  result;
	}
	public boolean delByMmh(int m){
		//check and delete AssignedSubject in table
		Subject sj = SubjectMod.getIsntance().getByMmh(m);
		if(sj!=null) {
			if(AssignedSubjectsMod.getIsntance().isExitSjidInTable(sj.getSjid())){
				if(!AssignedSubjectsMod.getIsntance().delBySjid(sj.getSjid()))return false;
			}
		}
		PreparedStatement delpd = null;
		connection = DB_Connection.getIsntance().getConnection();
		boolean result = false;
		try {
			delpd = connection.prepareStatement("DELETE FROM subject WHERE mmh = ? LIMIT 1");
			delpd.setInt(1, m);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  result;
	}
	public Subject getBySjid( int sid){
		Subject result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sql = connection.prepareStatement("SELECT sjid , mmh , name FROM subject WHERE sjid = ? ");
			sql.setInt(1,  sid);
			ResultSet rs = sql.executeQuery();
			while(rs.next()){
				int sjid = rs.getInt(1);
				int mmh = rs.getInt(2);
				String name = rs.getString(3);
				result = new Subject(sjid, mmh, name);
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			return null;
		}
		return result;
	}
	public Subject getByMmh( int m){
		Subject result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sql = connection.prepareStatement("SELECT sjid , mmh , name FROM subject WHERE mmh = ? ");
			sql.setInt(1,  m);
			ResultSet rs = sql.executeQuery();
			while(rs.next()){
				int sjid = rs.getInt(1);
				int mmh = rs.getInt(2);
				String name = rs.getString(3);
				result = new Subject(sjid, mmh, name);
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
		}
		return result;
	}
	public ArrayList<Subject> getAll(){
		ArrayList<Subject> result = null;
		PreparedStatement sqls = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql ="SELECT sjid , mmh , name FROM subject";
			sqls = connection.prepareStatement(sql);
			ResultSet rs = sqls.executeQuery();
			while(rs.next()){
				int sjid = rs.getInt(1);
				int mmh = rs.getInt(2);
				String name = rs.getString(3);
				result.add(new Subject(sjid, mmh, name));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
}
